package com.project.pet.mapper;
import com.project.pet.dto.HistoryDto;
import com.project.pet.pojo.History;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;


@Mapper
public interface HistoryMapper extends tk.mybatis.mapper.common.Mapper<History>{



//    @Select({
//            "select * from ( select b.id infoId,a.id historyId, b.category,a.browse_time borwseTime, b.title  ," +
//                    " ROW_NUMBER() over(PARTITION by a.info_id order by a.browse_time desc) rd  " +
//                    " from history a,info b where a.info_id = b.id  " +
//                    "  and a.wx_user_id=#{wxUserId}  " +
//                    "  group by   b.id ,a.id , b.category,a.browse_time,b.title  ) a  where rd=1 order by borwseTime desc limit 1000 "
//    })

    @Select({


            "SELECT infoId, historyId, category, borwseTime, title " +
                    "FROM ( " +
                    "  SELECT infoId, historyId, category, borwseTime, title, " +
                    "         @row_number := IF(@prev_infoId = infoId, @row_number + 1, 1) AS rd, " +
                    "         @prev_infoId := infoId AS dummy " +
                    "  FROM ( " +
                    "    SELECT b.id AS infoId, a.id AS historyId, b.category, a.browse_time AS borwseTime, b.title " +
                    "    FROM history a " +
                    "    JOIN info b ON a.info_id = b.id " +
                    "    WHERE a.wx_user_id = #{wxUserId} " +
                    "    GROUP BY b.id, a.id, b.category, a.browse_time, b.title " +
                    "    ORDER BY b.id, a.browse_time DESC " +
                    "  ) AS subquery, (SELECT @row_number := 0, @prev_infoId := NULL) AS vars " +
                    ") AS subquery2 " +
                    "WHERE rd = 1 " +
                    "ORDER BY borwseTime DESC " +
                    "LIMIT 1000;"


             })
    public List<HistoryDto> selectHistory(Integer wxUserId);
}
